************* NOTE: pause Dropbox synching before running!
	/* right-click Dropbox icon, left-click bottom left of the window */


*****************************************
* Foreign market returns with dividends *
*****************************************
	
* Import data in Stata and rename to country codes

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Australia.xlsx", firstrow
	gen cou = "AUS"
	save "data/AUS.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Austria.xlsx", firstrow
	gen cou = "AUT"
	save "data/AUT.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Belgium.xlsx", firstrow
	gen cou = "BEL"
	save "data/BEL.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Canada.xlsx", firstrow
	gen cou = "CAN"
	save "data/CAN.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Denmark.xlsx", firstrow
	gen cou = "DNK"
	save "data/DNK.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Finland.xlsx", firstrow
	gen cou = "FIN"
	save "data/FIN.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/France.xlsx", firstrow
	gen cou = "FRA"
	save "data/FRA.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Germany.xlsx", firstrow
	gen cou = "DEU"
	save "data/DEU.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Hong Kong.xlsx", firstrow
	gen cou = "HKG"
	save "data/HKG.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Ireland.xlsx", firstrow
	gen cou = "IRL"
	save "data/IRL.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Italy.xlsx", firstrow
	gen cou = "ITA"
	save "data/ITA.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Japan.xlsx", firstrow
	gen cou = "JPN"
	save "data/JPN.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Malaysia.xlsx", firstrow
	gen cou = "MYS"
	save "data/MYS.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Netherlands.xlsx", firstrow
	gen cou = "NLD"
	save "data/NLD.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/New Zealand.xlsx", firstrow
	gen cou = "NZL"
	save "data/NZL.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Norway.xlsx", firstrow
	gen cou = "NOR"
	save "data/NOR.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Singapore.xlsx", firstrow
	gen cou = "SGP"
	save "data/SGP.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Spain.xlsx", firstrow
	gen cou = "ESP"
	save "data/ESP.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Sweden.xlsx", firstrow
	gen cou = "SWE"
	save "data/SWE.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/Switzerland.xlsx", firstrow
	gen cou = "CHE"
	save "data/CHE.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries/UK.xlsx", firstrow
	gen cou = "GBR"
	save "data/GBR.dta", replace
	
* Merge into a panel
	clear all
	use "data/AUS.dta"
	foreach x in AUT BEL CAN DNK FIN FRA ///
	DEU HKG IRL ITA JPN MYS NLD ///
	NZL NOR SGP ESP SWE CHE GBR ///
	{
		merge m:m cou Year using "data/`x'.dta"
		drop _merge
	}
	rename Year year
	rename Mkt mkt
	save "data/mkt.dta", replace

* Eliminate individual series
	foreach x in AUS AUT BEL CAN DNK FIN FRA ///
	DEU HKG IRL ITA JPN MYS NLD ///
	NZL NOR SGP ESP SWE CHE GBR ///
	{
		erase "data/`x'.dta"
	}

	
********************************************
* Foreign market returns without dividends *
********************************************
	
* Import data in Stata and rename to country codes

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Australia.xlsx", firstrow
	gen cou = "AUS"
	save "data/AUS.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Austria.xlsx", firstrow
	gen cou = "AUT"
	save "data/AUT.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Belgium.xlsx", firstrow
	gen cou = "BEL"
	save "data/BEL.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Canada.xlsx", firstrow
	gen cou = "CAN"
	save "data/CAN.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Denmark.xlsx", firstrow
	gen cou = "DNK"
	save "data/DNK.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Finland.xlsx", firstrow
	gen cou = "FIN"
	save "data/FIN.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/France.xlsx", firstrow
	gen cou = "FRA"
	save "data/FRA.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Germany.xlsx", firstrow
	gen cou = "DEU"
	save "data/DEU.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Hong Kong.xlsx", firstrow
	gen cou = "HKG"
	save "data/HKG.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Ireland.xlsx", firstrow
	gen cou = "IRL"
	save "data/IRL.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Italy.xlsx", firstrow
	gen cou = "ITA"
	save "data/ITA.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Japan.xlsx", firstrow
	gen cou = "JPN"
	save "data/JPN.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Malaysia.xlsx", firstrow
	gen cou = "MYS"
	save "data/MYS.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Netherlands.xlsx", firstrow
	gen cou = "NLD"
	save "data/NLD.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/New Zealand.xlsx", firstrow
	gen cou = "NZL"
	save "data/NZL.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Norway.xlsx", firstrow
	gen cou = "NOR"
	save "data/NOR.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Singapore.xlsx", firstrow
	gen cou = "SGP"
	save "data/SGP.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Spain.xlsx", firstrow
	gen cou = "ESP"
	save "data/ESP.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Sweden.xlsx", firstrow
	gen cou = "SWE"
	save "data/SWE.dta", replace

	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/Switzerland.xlsx", firstrow
	gen cou = "CHE"
	save "data/CHE.dta", replace
	
	clear all
	import excel "orig/price_dividend_ratio/foreign_country_data/F-F_International_Countries_Wout_Div/UK.xlsx", firstrow
	gen cou = "GBR"
	save "data/GBR.dta", replace
	
* Merge into a panel
	clear all
	use "data/AUS.dta"
	foreach x in AUT BEL CAN DNK FIN FRA ///
	DEU HKG IRL ITA JPN MYS NLD ///
	NZL NOR SGP ESP SWE CHE GBR ///
	{
		merge m:m cou Year using "data/`x'.dta"
		drop _merge
	}
	rename Year year
	rename Mkt mkt_wo
	save "data/mkt_wo.dta", replace

* Eliminate individual series
	foreach x in AUS AUT BEL CAN DNK FIN FRA ///
	DEU HKG IRL ITA JPN MYS NLD ///
	NZL NOR SGP ESP SWE CHE GBR ///
	{
		erase "data/`x'.dta"
	}	
	clear all
	
	
*****************
* U.S. PD ratio *
*****************

* Import data from Excel and set time
	clear all
	import excel "orig/price_dividend_ratio/US_data/price_dividend_US.xlsx", firstrow
	gen cou = "USA"

* Calculate annual values
	foreach x in price dividend {
		egen `x'_y = mean(`x'), by (year)
	}
	collapse price_y dividend_y, by(cou year)
	foreach x in price dividend {
		rename `x'_y `x'
	}

* Generate price(t)/dividend(t+1) ratio
	destring year, replace
	tsset year
	gen pd = l.price / dividend
	tabstat pd, statistics(mean med min max)

	keep if year > 1949	
	save "data/pd_USA.dta", replace

	
**************************
* Country-level PD ratio *
**************************
	
* Merge with market returns with dividends
	clear all
	use "data/mkt.dta"
	merge m:m cou year using "data/mkt_wo.dta"
	drop _merge
	gen pd = 1 / ((mkt - mkt_wo)/100)
		******* intuition: mkt - mkt_wo gives the dividend yield (D/P), whose inverse is the price-dividend ratio (P/D)
	drop mkt mkt_wo
	destring year, replace

* Merge with the U.S. PD ratio	
	merge m:m cou year using "data/pd_USA.dta"
	drop _merge

* Delete single data set
	foreach x in pd_USA mkt mkt_wo {
	erase "data/`x'.dta"
	}
	
	save "data/pd.dta", replace
